package com.oros.db.jdbc;
import java.util.ArrayList;
import java.util.List;

import com.oros.db.AEFilter;
import com.oros.db.AERawItem;
import com.oros.db.AESort;
import com.oros.db.AETransactionData;
import com.oros.db.AEVaadTable;
import com.oros.db.AEColumnProperty;
import java.sql.*;

public class JdbcVaadTable extends AEVaadTable {

	private static final long serialVersionUID = 1L;
	private final int QUOTATION_OFFSET=1;
	private final int QUOTATION_MASK=3<<QUOTATION_OFFSET;
	
    public final int OPTION_IS_IMMEDIATE_COMMIT_REQUIRED=1;
    public final int OPTION_IS_QUOTATION_DOUBLEQUOTE=(0<<QUOTATION_OFFSET);
    public final int OPTION_IS_QUOTATION_SINGLEQUOTE=(1<<QUOTATION_OFFSET);
    public final int OPTION_IS_QUOTATION_BACKSINGLEQUOTE=(2<<QUOTATION_OFFSET);
    public final int OPTION_IS_QUOTATION_SLASHSINGLEQUOTE=(3<<QUOTATION_OFFSET);
    public final int OPTION_IS_QUOTATION_MARK_ESCAPED=8;
    public final int OPTION_IS_QUOTATION_MARK_REPLACED=0;
	
    protected String _driverDB,_urlDB,_userDB,_passwordDB;
        protected Connection _conDB;
        protected boolean isImmediateCommitRequired,isQuotationMarkEscaped;
        protected char QuoteChar;
        protected String QuoteReplace;
        public String TimeStampField ="Time_Stamp";
    public JdbcVaadTable()
    {
    	
    }
    public JdbcVaadTable(String _driverDB,String _urlDB,String _userDB,String _passwordDB,long options)
    {
    	init(_driverDB,_urlDB,_userDB,_passwordDB,options);
    }
    public void init(String _driverDB,String _urlDB,String _userDB,String _passwordDB,long options)
    {
        this._driverDB = _driverDB;
        this._urlDB = _urlDB;
        this._userDB = _userDB;
        this._passwordDB = _passwordDB;
        isImmediateCommitRequired=(options & OPTION_IS_IMMEDIATE_COMMIT_REQUIRED)!=0;
        isQuotationMarkEscaped=(options & OPTION_IS_QUOTATION_MARK_ESCAPED)!=0;
        switch((int)options & QUOTATION_MASK)
        {
        case OPTION_IS_QUOTATION_DOUBLEQUOTE:
        	QuoteChar='"';QuoteReplace=isQuotationMarkEscaped?"\\\"":"'";
        	break;
        case OPTION_IS_QUOTATION_SINGLEQUOTE:
    	    QuoteChar='\'';QuoteReplace=isQuotationMarkEscaped?"\\\'":"`";
    	    break;
        case OPTION_IS_QUOTATION_BACKSINGLEQUOTE:
        	QuoteChar='`';QuoteReplace=isQuotationMarkEscaped?"\\`":"'";
        	break;
        case OPTION_IS_QUOTATION_SLASHSINGLEQUOTE:
        	QuoteChar='´';QuoteReplace=isQuotationMarkEscaped?"\\´":"'";
        }
    }
    protected void openConnection() throws Exception
    {
        Class.forName(_driverDB);
        _conDB = DriverManager.getConnection(_urlDB, _userDB,_passwordDB);
    }
    protected void closeConnection() throws Exception
    {
        if (_conDB!=null)
        {
             _conDB.close();
             _conDB=null;
        }
    }
    protected String getQuotedString(String orig)
    {
    	if (orig==null) return "null";
    	StringBuilder dst=new StringBuilder();
    	dst.append(QuoteChar);
        int n=orig.length();
        for(int i=0;i<n;i++)
        {
        	char c=orig.charAt(i);
        	if (c==QuoteChar) dst.append(QuoteReplace);
        	else dst.append(c);
        }
    	dst.append(QuoteChar);
    	return dst.toString();
    }
    protected String getRequiredDataValue(Object data,Class<?> type)
    {
    	if (data==null) return "null";
    	if (type==Integer.class || type==Long.class) return data.toString();
    	return getQuotedString(data.toString());
    }
	@Override
	public void FinalTransactionCommit(AETransactionData arg0) throws Exception {
	}

	@Override
	public void FinalTransactionRollback(AETransactionData arg0)
			throws Exception {
	}
	@Override
	public List<Object> SelectRowsGeneral(String arg0, String arg1, int arg2,
			int arg3, ArrayList<AEFilter> arg4, ArrayList<AESort> arg5) throws Exception {
		return SelectRows(arg0,arg1,arg2,arg3,arg4,arg5);
	}
	@Override
	public void allocateNewId(AERawItem arg0, String arg1) throws Exception {
	}
	@Override
	public boolean vdIsTimeStampSupported() {
		return true;
	}
	@Override
	public boolean isNewItemHasDummyId() {
		return true;
	}
	@Override
	public boolean isCommitNeededRefresh() {
		return true;
	} 
  @Override
  public void vdSetFieldValue(Object entity, String fieldName, int fieldNr, Object fieldValue) throws Exception
  {
  }

  @Override
  public Object vdGetFieldValue(Object entity, String fieldName, int fieldNr)
  {
        Object[] dat = (Object[])entity;
        int datamnt=dat.length -1;
        if (fieldNr>=0 && fieldNr<datamnt) return dat[fieldNr];
        String[] cols=(String[])dat[datamnt];
        for(int i=datamnt-1;i>=0;i--)
        {
          if (cols[i].equals(fieldName)) return dat[i];
        }
        return null;
  }

  @Override
  public void vDeleteRowById(String tblname,String tblname_base, long id,AETransactionData transaction, boolean finalCommit,String primaryKey) throws Exception
  {
    openConnection();
    String deleteQuery="delete from "+tblname_base+" where "+primaryKey+" = "+id;
    Statement statement = _conDB.createStatement();
    statement.executeUpdate(deleteQuery);
    if (isImmediateCommitRequired) _conDB.commit();
    closeConnection();
  }
  protected String getPrimaryKeyForInsert(String tblname,long id) throws Exception
  {
	  return "null";
  }
  protected long executeInsertWithNewIdRequired(String query,Statement statement,String tablename) throws Exception
  {
	  statement.execute(query, Statement.RETURN_GENERATED_KEYS);
	  ResultSet rs = statement.getGeneratedKeys();
	  if (rs.next())
	  {
		  Object a = rs.getObject(1);
		  if (a instanceof Integer) return ((Integer)a).longValue();
		  if (a instanceof Long) return ((Long)a).longValue();
		  return new Long(Long.parseLong(a.toString()));
	  }
	  throw new Exception("Not supported generated keys getting");
  }
  @Override
  public void InsertUpdateRow(String tblname, String tblname_base, AERawItem item,AETransactionData transaction, boolean finalCommit) throws Exception
  {
    long id = item.getIdKey();
    openConnection();
    boolean timeStamped=false;
    long timeStamp = System.currentTimeMillis();
    StringBuilder query=new StringBuilder();
    Statement statement = _conDB.createStatement();
    int colamount = item.getRecordableColumnAmount();
    int keycolumn = item.getPrimaryColumnNumber();
    if (item.isNew())
    {
       query.append("insert into "+tblname_base+" VALUES(");
       String insSp="";
       for(int i=0;i<colamount;i++)
       {
    	   query.append(insSp);insSp=",";
    	   AEColumnProperty prop = item.getAEColumnProperty(i);
    	   if (i!=keycolumn)
    	   {
    		   String val=getRequiredDataValue(prop.getValue(), prop.getType());
    		   if (item.getColumnName(i).equals(TimeStampField))
    		   {
    			   val=Long.toString(timeStamp);
    			   timeStamped=true;
    		   }
    		   query.append(val);
    	   }
    	   else if (id>0)
    	   {
    		   query.append(Long.toString(id));
    	   }
    	   else 
    	   {
    		   query.append(getPrimaryKeyForInsert(tblname_base,id));
    	   }
       }
       if (!timeStamped)
       {
    	   query.append(insSp);
    	   String val=Long.toString(timeStamp);
    	   query.append(val);
       }
       query.append(')');
       if (id>0) statement.executeUpdate(query.toString());
       else
       {
    	   id=executeInsertWithNewIdRequired(query.toString(),statement,tblname_base);
    	   if (id>0) 
    	   {
    		   item.setPrimaryKey(id);
    	   }
       }
    }
    else
    {
       query.append("update "+tblname_base+" set");
       char colsep=' ';
       for(int i=0;i<colamount;i++) if (i!=keycolumn)
       {
    	   AEColumnProperty prop = item.getAEColumnProperty(i);
           query.append(colsep);
           colsep=',';
           String colname=item.getColumnName(i);
           query.append(colname);
           query.append('=');
           String val=getRequiredDataValue(prop.getValue(),prop.getType());
           if (colname.equals(TimeStampField))
           {
        	   val=Long.toString(timeStamp);
        	   timeStamped = true;
           }
           query.append(val);
       }
       if (!timeStamped)
       {
    	   query.append(colsep);
    	   query.append(TimeStampField);
    	   query.append('=');
    	   query.append(Long.toString(timeStamp));
       }
       query.append(" where ");
       query.append(item.getColumnName(keycolumn));
       query.append('=');
       query.append(Long.toString(id));
       statement.executeUpdate(query.toString());
    }
    if (isImmediateCommitRequired) _conDB.commit();
    item.commit(timeStamp);
    closeConnection();
  }
  protected void placeOperatorWhereClause(StringBuilder s,String columnName,Class<?> type,Object val,AEFilter.FilterOperator operator)
  {
	  s.append(columnName);
	  switch(operator)
	  {
	  case EQUAL:
		  s.append('=');
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case GREATER_THAN:
		  s.append(">");
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case GREATER_THAN_OR_EQUAL:
		  s.append(">=");
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case LESS_THAN:
		  s.append("<");
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case LESS_THAN_OR_EQUAL:
		  s.append("<=");
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case NOT_EQUAL:
		  s.append("!=");
		  s.append(getRequiredDataValue(val,type));
		  break;
	  case IN:
		  s.append(" in (");
		  Object[] pool = (Object[])val;
		  int n=pool.length;String sinSp="";
		  for(int i=0;i<n;i++)
		  {
			  s.append(sinSp);sinSp=",";
		      s.append(getRequiredDataValue(pool[i],type));
		  }
		  s.append(")");
		  break;		  
	  }
  }
  protected void placeOrderByColumn(StringBuilder s,String columnName,boolean isDescending)
  {
	  s.append(columnName);
	  if (isDescending)
		  s.append(" DESC");
  }
  protected void placeOffsetAndLimit(StringBuilder s,int limit,int offset)
  {
	  
  }
  public String getAdditionalClauses(String tblname,int limit,int offset,ArrayList<AEFilter> aeFilter,ArrayList<AESort> aeSort)
  {
	  StringBuilder s=new StringBuilder();
	  int filtSz=aeFilter==null?0:aeFilter.size();
	  String whereSep=" where (";
      for(int i=0;i<filtSz;i++)
      {
    	  s.append(whereSep);
    	  whereSep="and(";
    	  AEFilter filter = aeFilter.get(i);
    	  String columnName=filter.getColumnName();
    	  Class<?> type=filter.getType();
    	  Object val = filter.getValue();
    	  AEFilter.FilterOperator operator = filter.getFilterOperator();
    	  placeOperatorWhereClause(s,columnName,type,val,operator);
    	  s.append(')');
      }
      if (filtSz>0) s.append(' ');
      int sortSz=aeSort==null?0:aeSort.size();
      String orderSep=" order by ";
      for(int i=0;i<sortSz;i++)
      {
    	  AESort sort = aeSort.get(i);
    	  s.append(orderSep);
    	  orderSep=",";
    	  String columnName=sort.getColumnName();
    	  boolean desc = sort.getSortDirection()==AESort.SortDirection.DESCENDING;
    	  placeOrderByColumn(s,columnName,desc);
      }
      placeOffsetAndLimit(s,limit,offset);
	  return s.toString();
  }

  @Override
  public List<Object> SelectRows(String tblname,String tblname_base,int limit, int offset, ArrayList<AEFilter> aeFilter, ArrayList<AESort> aeSort) throws Exception
  {
    openConnection();
    String additionalClauses = getAdditionalClauses(tblname_base,limit,offset,aeFilter,aeSort);
    String selectString = additionalClauses!=null && additionalClauses.trim().length()>0?"select * from "+tblname_base+" "+additionalClauses:
    	      "select * from "+tblname_base;
    List<Object> list = new ArrayList<Object>();
    Statement statement = _conDB.createStatement();
    ResultSet rsset = statement.executeQuery(selectString);
    ResultSetMetaData metaData = rsset.getMetaData();
    int colamnt = metaData.getColumnCount();
    if (colamnt>0)
    {
        String[] cols=new String[colamnt];
        for(int i=0;i<colamnt;i++) cols[i]=metaData.getColumnName(i+1);
        int objamnt=colamnt+1;
        while (rsset.next())
        {
            Object[] dat=new Object[objamnt];
            for(int i=0;i<colamnt;i++) dat[i]=rsset.getObject(cols[i]);
            dat[colamnt]=cols;
            list.add((Object)dat);
        }
    }
    closeConnection();
    return list;
  }

  @Override
  public long vvdGetPrimaryId(Object entity,String idColumnName,int idColumnNr)
  {
    Object o = vdGetFieldValue(entity,idColumnName,idColumnNr);
    if (o instanceof Integer) return ((Integer)o).longValue();
    if (o instanceof Long) return ((Long)o).longValue();
    return -1;
  }

  @Override
  public Object vdGetTimeStamp(Object entity)
  {
    return vdGetFieldValue(entity,TimeStampField,-1);
  }
  @Override
  public boolean isBaseTableSeparationSupported()
  {
		return false;
  }  
	@Override 
	public boolean isAEFilterAndSorterSupported()
	{
		return true;
	}

}
